package comfour;

import comfour.util.BaseJdbc;
import comfour.vo.StudentVo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Demo5 {
    public static void main(String[] args) throws Exception {
        //2：执行sql
        String sql=" SELECT  e.`ExamDate`,d.`SubjectName` AS jectName, " +
                " (e.`StudentResult`) AS avgd," +
                " MAX(StudentResult) AS  maxresult , " +
                " MIN(StudentResult) AS minresult " +
                " FROM `result` e" +
                " LEFT JOIN `subject` d" +
                " ON e.`SubjectNo`= d.`SubjectNo`" +
                " GROUP BY d.`SubjectNo`" +
                " HAVING  avgd>?";
        Object[] obj = new Object[1];
        obj[0]=80;
        ResultSet resultSet = BaseJdbc.getBaseSelect(sql, obj);
        List<StudentVo> studnetList = new ArrayList<StudentVo>();
        while (resultSet.next()){ // 映射
            StudentVo studnet = new StudentVo();
            studnet.setExactime(resultSet.getString("ExamDate"));
            studnet.setAvgResult(resultSet.getDouble("avgd"));
            studnet.setMaxResult(resultSet.getDouble("maxresult"));
            studnet.setMinResult(resultSet.getDouble("minresult"));
            studnetList.add(studnet);
        }
        for (StudentVo studnet:studnetList) {
            System.out.println(studnet.getExactime()+"\t\t"+studnet.getAvgResult()+"\t\t"+studnet.getMinResult());
        }

    }
}
